﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
using Earthwatchers.Models;
using Microsoft.SqlServer.Types;
using System.Data.SqlTypes;

namespace Earthwatchers.Data
{
    public class SatelliteImageRepository : ISatelliteImageRepository
    {
        private readonly IDbConnection connection;

        public SatelliteImageRepository(string connectionString)
        {
            connection = new SqlConnection(connectionString);
        }

        public SatelliteImage Get(int id)
        {
            connection.Open();
            var satelliteImage = connection.Query<SatelliteImage>("select Id,extent.STAsText() as Wkt,Name,Provider,Published, AcquisitionDate,ImageType, MinLevel, MaxLevel, UrlMetadata from SatelliteImage s where s.Id=@Id", new { Id = id }).FirstOrDefault();
            connection.Close();
            AddExtent(satelliteImage);
            return satelliteImage;
        }

        public List<SatelliteImage> GetAll()
        {
            connection.Open();
            var sql = String.Format(@"select Id,extent.STAsText() as Wkt, Name,Provider,Published,AcquisitionDate, ImageType, UrlTileCache, MinLevel, MaxLevel, UrlMetadata from SatelliteImage");
            var satelliteImages = connection.Query<SatelliteImage>(sql).ToList();
            connection.Close();
            UpdateList(satelliteImages);
            return satelliteImages;
        }

        public List<SatelliteImage> Intersects(string wkt)
        {
            connection.Open();
            string sql = String.Format(@"select Id,extent.STAsText() as Wkt, Name,Provider,Published,AcquisitionDate,ImageType, UrlTileCache, MinLevel, MaxLevel, UrlMetadata from SatelliteImage
             where extent.STIntersects(geography::STGeomFromText('{0}', 4326))=1 ", wkt);
            var satelliteImages = connection.Query<SatelliteImage>(sql).ToList();
            connection.Close();
            UpdateList(satelliteImages);
            return satelliteImages.ToList();
        }

        private void AddExtent(SatelliteImage satelliteImage)
        {
            var geom=SqlGeometry.STGeomFromText(new SqlChars(satelliteImage.Wkt),4326);
            var xmin=geom.STEnvelope().STPointN(1).STX.Value;
            var ymin = geom.STEnvelope().STPointN(1).STY.Value;
            var xmax = geom.STEnvelope().STPointN(3).STX.Value;
            var ymax = geom.STEnvelope().STPointN(3).STY.Value;
            var extent=new Extent(xmin,ymin,xmax,ymax, 4326);
            satelliteImage.Extent = extent;
        }


        public void UpdateList(List<SatelliteImage> images)
        {
            foreach (var satelliteImage in images)
            {
                AddExtent(satelliteImage);
            }
        }

        public void Update(int id, SatelliteImage satelliteImage)
        {
            throw new NotImplementedException();
        }

        public SatelliteImage Insert(SatelliteImage satelliteImage)
        {
            connection.Open();
            if(satelliteImage.AcquisitionDate == null) satelliteImage.AcquisitionDate = DateTime.Now;
            if (satelliteImage.Published == null) satelliteImage.Published = DateTime.Now;

            var cmd = connection.CreateCommand();
            cmd.CommandText = "insert into SatelliteImage(extent,Name,Provider,Published,AcquisitionDate,ImageType, UrlTileCache, MinLevel, MaxLevel, UrlMetadata) values(@extent,@name,@provider,@published,@acquisitiondate,@imagetype,@urltilecache,@minlevel,@maxlevel,@urlmetadata) SET @ID = SCOPE_IDENTITY()";
            cmd.Parameters.Add(new SqlParameter("@extent", SqlGeography.STGeomFromText(new SqlChars(satelliteImage.Wkt), 4326)) { UdtTypeName = "Geography" });
            cmd.Parameters.Add(new SqlParameter("@name", satelliteImage.Name));
            cmd.Parameters.Add(new SqlParameter("@provider", satelliteImage.Provider));
            cmd.Parameters.Add(new SqlParameter("@imagetype", satelliteImage.ImageType));
            cmd.Parameters.Add(new SqlParameter("@urltilecache", (object)satelliteImage.UrlTileCache?? DBNull.Value));
            cmd.Parameters.Add(new SqlParameter("@published", satelliteImage.Published));
            cmd.Parameters.Add(new SqlParameter("@acquisitiondate", satelliteImage.AcquisitionDate));
            cmd.Parameters.Add(new SqlParameter("@minlevel", satelliteImage.MinLevel));
            cmd.Parameters.Add(new SqlParameter("@maxlevel", satelliteImage.MaxLevel));
            cmd.Parameters.Add(new SqlParameter("@urlmetadata", (object)satelliteImage.UrlMetadata ?? DBNull.Value));
            var idParameter = new SqlParameter("@ID", SqlDbType.Int) {Direction = ParameterDirection.Output};
            cmd.Parameters.Add(idParameter);
            cmd.ExecuteNonQuery();

            var id = (int)idParameter.Value;
            satelliteImage.Id = id;
            connection.Close();
            return satelliteImage;
        }

        public void Delete(int id)
        {
            connection.Open();
            connection.Execute("delete from SatelliteImage where id=@id", new { id });
            connection.Close();
        }
    }
}
